import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'] = ['SimHei']

path = '../result/result3.xlsx'
data = pd.read_excel(path, None)  # 读取数据,设置None可以生成一个字典，字典中的key值即为sheet名字，此时不用使用DataFram，会报错
print(data.keys())  # 查看sheet的名字
resss = 0
x_axis_data = []
y_axis_chengben = []
for sh_name in data.keys():
    # print('sheet_name的名字是：', sh_name)
    x_axis_data.append(sh_name)
    sh_data = pd.DataFrame(pd.read_excel(path, sh_name))  # 获得每一个sheet中的内容
    data = sh_data[:81][
        ['黄豆', '黑豆', '红豆', '绿豆', '爬豆', '小麦', '玉米', '谷子', '高粱', '黍子', '荞麦', '南瓜', '红薯', '莜麦',
         '大麦', '水稻', '豇豆', '刀豆', '芸豆', '土豆', '西红柿', '茄子', '菠菜 ', '青椒', '菜花', '包菜', '油麦菜',
         '小青菜', '黄瓜', '生菜 ', '辣椒', '空心菜', '黄心菜', '芹菜', '大白菜', '白萝卜', '红萝卜', '榆黄菇', '香菇',
         '白灵菇', '羊肚菌']]
    # print(data.values)
    arr = data.values
    # print(arr.shape)
    parm = set()
    sum = 0.0
    crop = ['黄豆', '黑豆', '红豆', '绿豆', '爬豆', '小麦', '玉米', '谷子', '高粱', '黍子', '荞麦', '南瓜', '红薯',
            '莜麦',
            '大麦', '水稻', '豇豆', '刀豆', '芸豆', '土豆', '西红柿', '茄子', '菠菜 ', '青椒', '菜花', '包菜', '油麦菜',
            '小青菜', '黄瓜', '生菜 ', '辣椒', '空心菜', '黄心菜', '芹菜', '大白菜', '白萝卜', '红萝卜', '榆黄菇',
            '香菇',
            '白灵菇', '羊肚菌']
    total = [400, 500, 400, 350, 415, 800, 1000, 400, 630, 525, 110, 3000, 2200, 420, 525, 500, 3000, 2000, 3000, 2000,
             2400, 6400, 2700, 2400, 3300, 3700, 4100, 3200, 12000, 4100, 1600, 10000, 5000, 5500, 5000, 4000, 3000,
             5000, 4000, 10000, 1000]
    crop_prices = [3.25, 7.5, 8.25, 7, 6.75, 3.5, 3, 6.75, 6, 7.5, 40, 1.5, 3.25, 5.5, 3.5, 7, 8, 6.75, 6.5, 3.75, 6.25,
                   5.5, 5.75, 5.25, 5.5, 6.5, 5, 5.75, 7, 5.25, 7.25, 4.5, 4.5, 4, 2.5, 2.5, 3.25, 57.5, 19, 16, 100]
    chengben_arr = [400, 400, 350, 350, 350, 450, 500, 360, 400, 360, 350, 1000, 2000, 400, 350, 680, 2000, 1000, 2000,
                    2000, 2000, 2000, 2300, 1600, 2400, 2900, 1600, 1600, 2900, 1600, 1000, 4100, 2000, 900, 2000, 500,
                    500, 3000, 2000, 10000, 10000]

    print("=====================")
    # print(len(total))
    # print(len(crop_prices))
    sin_price = 0
    chengben = 0

    for i in range(0, 41):
        for j in range(0, 81):
            sum = sum + float('%.2f' % arr[j][i])
        chengben = chengben + (int(sum * chengben_arr[i]))
        sin_price = sin_price + (int(sum * total[i] * crop_prices[i]))
        sum = 0.0
    print(sh_name + "年的总产值")
    print(sin_price)
    print(sh_name + "年的总成本")
    y_axis_chengben.append(chengben)
    print(chengben)
    resss = resss + sin_price - chengben
    sin_price = 0
    chengben = 0
print("7年的总利润\n")
print(resss)
resss = 0
plt.plot(x_axis_data, y_axis_chengben, 'b*--', alpha=0.5, linewidth=1, label='acc')  # 'bo-'表示蓝色实线，数据点实心原点标注
## plot中参数的含义分别是横轴值，纵轴值，线的形状（'s'方块,'o'实心圆点，'*'五角星   ...，颜色，透明度,线的宽度和标签 ，

plt.legend()  # 显示上面的label
plt.xlabel('年份')  # x_label
plt.ylabel('成本')  # y_label
plt.ylim((600000, 800000))
# plt.ylim(-1,1)#仅设置y轴坐标范围
plt.show()
